import logging

from sqlTools.mysqlConnect import mysql_db

# from playhouse.pool import PooledPostgresqlExtDatabase
#
# from tools.configmanager import config
#
# # 简单连接 db = PostgresqlDatabase('video',user='postgres',password='example',host='localhost')
# # 连接池
# db = PooledPostgresqlExtDatabase(database=config.get('node.postgresql.database.0.dbname'), max_connections=20,
#                                  stale_timeout=300,
#                                  # 5 minutes. 当连接超过其 stale_timeout 时，连接不会完全关闭。相反，只有在请求新连接时，才会关闭过时的连接。
#                                  user=config.get('node.postgresql.database.0.user'),
#                                  password=config.get('node.postgresql.database.0.pwd'),
#                                  host=config.get('node.postgresql.ip'), port=config.get('node.postgresql.port'), )

# 原生sql，返回游标（Cursor）
db = mysql_db(host="x.x.x.x", port=3306, database="test", user="test", pwd=123)

sql = """
      SELECT u.username, p.title
      FROM users AS u
               JOIN posts AS p ON u.id = p.user_id
      WHERE u.is_active = %s
      ORDER BY p.created_at DESC \
      """
params = (True,)  # 参数化查询防止 SQL 注入
with db:
    cursor = db.execute_sql(sql, params)
    for row in cursor:
        print(row)  # 直接处理原始结果集
        logging.info(row[1])

# 参数化防止sql注入。SQL()不安全，要用raw()和RawSQL()；
# 注意 表名 或 字段名 无法参数化!
# query = User.raw(f'SELECT * FROM {table_name} WHERE {id} = %s', [1])  # 需手动校验 table_name 和 id字段名

# 默认省略 FROM 子句, 是类名的小写复数，如 User → users。除非model中定义了表名。
# query = User.raw('SELECT * WHERE id = %s', [1])
#
# # sql片段 用RawSQL()。
# from peewee import RawSQL
# query = User.select().where(RawSQL('u.is_active = %s', [True]))
# # 同时演示多个sql片段，还有多表联合查询,改写from。
# query = User
#          .select(User.username, RawSQL('p.title', []))
#          .from_(RawSQL('users AS u JOIN posts AS p ON u.id = p.user_id', []))
#          .where(RawSQL('u.is_active = %s', [True]))
#          .order_by(RawSQL('p.created_at DESC', []))
# for row in query:
#     print(row.username, row.title)
#
# # 如果频繁需要复杂 SQL，可以扩展 Peewee 模型，封装常用 RawSQL：
# class User(Model):
#     @classmethod
#     def active_users(cls):
#         return cls.select().where(RawSQL("is_active = %s AND last_login > %s", [True, '2023-01-01']))
# # 调用
# query = User.active_users()
